package com.irdstudio.tdp.executor.core.plugin;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * TODO 封装JDBC查询
 * 
 * @author Mr.Fang
 * @email luck_2088@163.com
 * @date 2019年1月22日 下午5:10:28
 *
 */
public class DaoUtil {
	
	private static final Logger logger = LoggerFactory.getLogger(DaoUtil.class);
	/**
	 * 查询数据返回 Collection<Map<String, String>>
	 * 
	 * @param sql
	 * @param params
	 * @param con
	 * @return
	 * @throws Exception
	 */
	public static Collection<Map<String, String>> executeQuery(String sql, Object[] params, Connection con)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData rm = null;

		try {
			ps = con.prepareStatement(sql);
			ps.clearParameters();

			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}

			rs = ps.executeQuery();
			rm = rs.getMetaData();
			Collection<Map<String, String>> col = new ArrayList<Map<String, String>>();
			while (rs.next()) {
				Map<String, String> mp = new HashMap<String, String>();
				for (int i = 1; i <= rm.getColumnCount(); i++) {
					if (Types.BLOB == rm.getColumnType(i)) {
						mp.put(rm.getColumnName(i).toLowerCase(), blob2String(rs.getBlob(i)));
					} else if (Types.CLOB == rm.getColumnType(i)) {
						mp.put(rm.getColumnName(i).toLowerCase(), clob2String(rs.getClob(i)));
					} else {
						mp.put(rm.getColumnName(i).toLowerCase(), rs.getString(i));
					}
				}
				col.add(mp);
			}
			return col;
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			close(rs, null, ps);
		}
	}

	/**
	 * 查询数据返回 Map<String, String>
	 * 
	 * @param sql
	 * @param params
	 * @param con
	 * @return
	 * @throws Exception
	 */
	public static Map<String, String> getFirstRecord(String sql, Object[] params, Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, String> mp = null;
		try {
			ps = con.prepareStatement(sql);
			ps.clearParameters();

			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rm = rs.getMetaData();
			mp = new HashMap<String, String>();
			if (rs.next()) {
				for (int i = 1; i <= rm.getColumnCount(); i++) {
					if (Types.BLOB == rm.getColumnType(i)) {
						mp.put(rm.getColumnName(i).toLowerCase(), blob2String(rs.getBlob(i)));
					} else {
						mp.put(rm.getColumnName(i).toLowerCase(), rs.getString(i));
					}
				}
			}
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			close(rs, null, ps);
		}
		return mp;
	}

	/**
	 * 执行sql语句
	 * 
	 * @param sql
	 * @param params
	 * @param con
	 * @return
	 * @throws Exception
	 */
	public static boolean execute(String sql, Object[] params, Connection con) throws Exception {
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.clearParameters();
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}
			return ps.execute();
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			close(null, null, ps);
		}
	}

	/**
	 * 执行sql语句
	 * 
	 * @param sql
	 * @param params
	 * @param con
	 * @return
	 * @throws Exception
	 */
	public static int executeUpdate(String sql, Object[] params, Connection con) throws Exception {
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.clearParameters();

			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject((i + 1), params[i]);
				}
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e);
		} finally {
			close(null, null, ps);
		}
	}

	/**
	 * Blob转String
	 * 
	 * @param blob Blob
	 * @return String
	 */
	public static String blob2String(Blob blob) {
		if (null == blob) {
			return "";
		}
		String str = "";
		try {
			str = new String(blob.getBytes(1, (int) blob.length()), "UTF-8");
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
		return str;
	}

	/**
	 * clob转String
	 * 
	 * @param clob
	 * @return
	 */
	public static String clob2String(Clob clob) throws Exception {// Clob转换成String 的方法
		if (null == clob) {
			return "";
		}
		String content = "";
		StringBuilder sb = new StringBuilder();
		Reader inStream = null;
		BufferedReader bfRead = null;
		try {
			inStream = clob.getCharacterStream(); // 取得大字段对象数据输出流
			bfRead = new BufferedReader(inStream);
			String s = bfRead.readLine();
			while (s != null) {// 执行循环将字符串全部取出付值
				sb.append(s);
				s = bfRead.readLine();
			}
			content = sb.toString();
		} catch (Exception ex) {
			throw new Exception(ex);
		} finally {
			try {
				if (inStream != null) {
					inStream.close();
				}
			} catch (Exception e) {
			}
			try {
				if (bfRead != null) {
					bfRead.close();
				}
			} catch (Exception e) {
			}
		}
		return content;
	}

	/**
	 * 
	 * TODO 关闭资源
	 * 
	 * @param rs
	 * @param s
	 * @param ps
	 * @author Mr.Fang
	 * @email luck_2088@163.com
	 * @date 2019年1月22日 下午5:18:36
	 */
	public static void close(ResultSet rs, Statement s, PreparedStatement ps) {
		try {
			if (rs != null)
				rs.close();
			if (s != null)
				s.close();
			if (ps != null)
				ps.close();
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
	}

}
